using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Dapper;
using Dapper_Core.Basic;
using Dapper_Core.DAL.Interface;
using Dapper_Core.Model;
using ExpressionToWhere;

namespace Dapper_Core.DAL.Impl
{
    public class BaseRepository<T> : IBaseRepository<T> 
        where T : BaseModel, new()
    {
        private DBEntity dbEntity;
        private bool syncDetail;
        private bool syncReference;
        public TransactionContext transactionContext { get; private set; }
        public IDbConnection connection { get; private set; }
        public IDbTransaction transaction { get; private set; }

        //设置系统默认数据库
        public BaseRepository(bool syncReference = false, bool syncDetail = false) : this(DBEnum.ADMP, syncReference, syncDetail)
        {
            
        }

        public BaseRepository(DBEntity dbEntity, bool syncReference = false, bool syncDetail = false)
        {
            this.dbEntity = dbEntity;
            this.syncDetail = syncDetail;
            this.syncReference = syncReference;
            this.connection = ConnectionFactory.GetOpenConnection(dbEntity);
        }

        public BaseRepository(TransactionContext transactionContext, bool syncReference = false, bool syncDetail = false)
        {
            if (this.connection != null && this.connection.State == ConnectionState.Open)
            {
                this.connection.Close();
                this.connection.Dispose();
            }
            this.syncDetail = syncDetail;
            this.syncReference = syncReference;
            this.transactionContext = transactionContext;
            this.connection = transactionContext.Connection;
        }

        /// <summary>
        /// 初始化事务上下文
        /// </summary>
        /// <param name="transactionContext"></param>
        protected TransactionContext InitialTransaction()
        {
            //若事务对象已初始化且数据库连接可用则直接返回（通过含参构造方法创建实例）
            if (this.transactionContext != null && this.connection != null)
            {
                return this.transactionContext;
            }
            //若事务对象未初始化但数据库连接可用则先关闭现有连接再初始化事物对象（通过无参构造方法创建实例）
            if (this.connection != null && this.connection.State == ConnectionState.Open)
            {
                this.connection.Close();
                this.connection.Dispose();
            }
            this.transactionContext = new TransactionContext();
            this.connection = this.transactionContext.Connection;
            return this.transactionContext;
        }

        #region 增
        public bool Add(T model, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.Insert<string, T>(model, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool Add<TKey>(T model, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.Insert<TKey, T>(model, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool AddList(IEnumerable<T> modelList, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                foreach (T model in modelList)
                {
                    connection.Insert<string, T>(model, transaction, commandTimeout);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool AddList<TKey>(IEnumerable<T> modelList, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                foreach (T model in modelList)
                {
                    connection.Insert<TKey, T>(model, transaction, commandTimeout);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region 删
        public bool Delete(object id, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.Delete<T>(id, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool Delete(T model, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.Delete(model, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool DeleteList(IEnumerable<T> modelList, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                foreach (T model in modelList)
                {
                    connection.Delete(model, transaction, commandTimeout);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool DeleteList(Expression<Func<T, bool>> exp, bool searchNull = false, int? commandTimeout = null)
        {
            (string where, Dictionary<string, object> parameters) = exp.ToWhereParametric(searchNull, true, dbEntity.DBType.ToString());
            return DeleteList(where, parameters, commandTimeout);
        }

        public bool DeleteList(string where, object parameters = null, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.DeleteList<T>(where, parameters, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool DeleteList(object whereConditions, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.DeleteList<T>(whereConditions, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool SoftDelete(Expression<Func<T, bool>> exp, string deleter, DateTime deleteDate, bool searchNull = false, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                (string where, Dictionary<string, object> parameters) = exp.ToWhereParametric(searchNull, true, dbEntity.DBType.ToString());
                if (!parameters.ContainsKey("@deleter"))
                {
                    parameters.Add("@deleter", deleter);
                }
                if (!parameters.ContainsKey("@deleteDate"))
                {
                    parameters.Add("@deleteDate", deleteDate);
                }
                connection.SoftDelete<T>(where, parameters, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region 改
        public bool Update(T model, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.Update(model, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool UpdateList(IEnumerable<T> modelList, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                foreach (T model in modelList)
                {
                    connection.Update(model, transaction, commandTimeout);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool UpdateNotNull(T model, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                connection.UpdateNotNull(model, transaction, commandTimeout);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public bool UpdateNotNullList(IEnumerable<T> modelList, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                foreach (T model in modelList)
                {
                    connection.UpdateNotNull(model, transaction, commandTimeout);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region 查
        public T Get(object id, int? commandTimeout = null)
        {
            try
            {
                if (id == null) return null;
                InitialState();
                T result = connection.Get<T>(id, transaction, commandTimeout);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public T GetFirst(string where, object parameters = null, string orderby = null, int? commandTimeout = null)
        {
            IEnumerable<T> result = GetList(where, parameters, orderby, commandTimeout);
            if (!result.HasData()) return null;
            return result.First();
        }

        public IEnumerable<T> GetList(Expression<Func<T, bool>> exp, bool searchNull = false, string orderby = null, int? commandTimeout = null)
        {
            //参数化查询
            //(string where, Dictionary<string, object> parameters) = exp.ToWhereParametric(searchNull, true, dbEntity.DBType.ToString());
            //return GetList(where, parameters, orderby, commandTimeout);
            //非参数化查询
            string where = exp.ToWhereNonParametric(searchNull, true, dbEntity.DBType.ToString());
            IEnumerable<T> result = GetList(where, null, orderby, commandTimeout);
            return result;
        }

        public IEnumerable<T> GetList(string where, object parameters = null, string orderby = null, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                IEnumerable<T> result = connection.GetList<T>(where, parameters, orderby, transaction, commandTimeout);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public IEnumerable<T> GetList(object whereConditions, string orderby = null, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                IEnumerable<T> result = connection.GetList<T>(whereConditions, transaction, commandTimeout);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public int GetListCount(Expression<Func<T, bool>> exp, bool searchNull = false, int? commandTimeout = null)
        {
            //参数化查询
            (string where, Dictionary<string, object> parameters) = exp.ToWhereParametric(searchNull, true, dbEntity.DBType.ToString());
            return GetListCount(where, parameters, commandTimeout);
            //非参数化查询
            //string where = exp.ToWhereNonParametric(searchNull, true, dbEntity.DBType.ToString());
            //return GetListCount(where, null, commandTimeout);
        }

        public int GetListCount(string where, object parameters = null, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                return connection.RecordCount<T>(where, parameters, transaction, commandTimeout);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        public IEnumerable<T> GetPagelist(int pageNumber, int rowsPerPage, Expression<Func<T, bool>> exp, string orderby, bool searchNull = false, int? commandTimeout = null)
        {
            //参数化查询
            (string where, Dictionary<string, object> parameters) = exp.ToWhereParametric(searchNull, true, dbEntity.DBType.ToString());
            IEnumerable<T> result = GetPagelist(pageNumber, rowsPerPage, where, orderby, parameters, commandTimeout);
            return result;
            //非参数化查询
            //string where = exp.ToWhereNonParametric(searchNull, true, dbEntity.DBType.ToString());
            //return GetPagelist(pageNumber, rowsPerPage, where, orderby, null, commandTimeout);
        }

        public IEnumerable<T> GetPagelist(int pageNumber, int rowsPerPage, string where, string orderby, object parameters = null, int? commandTimeout = null)
        {
            try
            {
                InitialState();
                IEnumerable<T> result = connection.GetListPaged<T>(pageNumber, rowsPerPage, where, orderby, parameters, transaction, commandTimeout);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region 执行SqlMap.xml中的Sql语句
        /// <summary>
        /// 根据id执行SqlMap.xml中对应的Sql语句
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="webParas"></param>
        /// <returns></returns>
        public IEnumerable<T> QueryMap(string sqlId, Dictionary<string, string> webParas)
        {
            try
            {
                InitialState();
                List<string> sqlParaList = new List<string>();
                string sql = ReadSQLXml.CreatSqlParaSql(sqlId, webParas, ref sqlParaList);
                var parameters = new DynamicParameters();
                foreach (var item in sqlParaList)
                {
                    parameters.Add(item, webParas[item]);
                }
                IEnumerable<T> result = connection.Query<T>(sql, parameters, transaction, commandType: CommandType.Text);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 根据id执行SqlMap.xml中对应的Sql语句
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="webParas"></param>
        /// <returns></returns>
        public T QueryMapModel(string sqlId, Dictionary<string, string> webParas)
        {
            try
            {
                return QueryMap(sqlId, webParas).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 根据id执行SqlMap.xml中对应的Sql语句
        /// </summary>
        /// <typeparam name="T2"></typeparam>
        /// <param name="sqlId"></param>
        /// <param name="webParas"></param>
        /// <returns></returns>
        public IEnumerable<T2> QueryMap<T2>(string sqlId, Dictionary<string, string> webParas)
        {
            try
            {
                InitialState();
                List<string> sqlParaList = new List<string>();
                string sql = ReadSQLXml.CreatSqlParaSql(sqlId, webParas, ref sqlParaList);
                var parameters = new DynamicParameters();
                foreach (var item in sqlParaList)
                {
                    parameters.Add(item, webParas[item]);
                }
                return connection.Query<T2>(sql, parameters, transaction, commandType: CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 根据id执行SqlMap.xml中对应的Sql语句
        /// </summary>
        /// <typeparam name="T2"></typeparam>
        /// <param name="sqlId"></param>
        /// <param name="webParas"></param>
        /// <returns></returns>
        public T2 QueryMapModel<T2>(string sqlId, Dictionary<string, string> webParas)
        {
            try
            {
                return QueryMap<T2>(sqlId, webParas).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parameters">所需参数</param>
        /// <returns></returns>
        public bool ExecuteProc(string procName, object parameters)
        {
            try
            {
                InitialState();
                connection.Execute(procName, parameters, transaction, commandType: CommandType.StoredProcedure);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parameters">所需参数</param>
        /// <returns></returns>
        public object ExecuteProcScalar(string procName, object parameters)
        {
            try
            {
                InitialState();
                return connection.ExecuteScalar(procName, parameters, transaction, commandType: CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parameters">所需参数</param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteProcQuery(string procName, object parameters)
        {
            try
            {
                InitialState();
                IEnumerable<T> result = connection.Query<T>(procName, parameters, transaction, commandType: CommandType.StoredProcedure);
                AfterSelect(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parameters">所需参数</param>
        /// <returns></returns>
        public T ExecuteProcModel(string procName, object parameters)
        {
            try
            {
                return ExecuteProcQuery(procName, parameters).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        #endregion

        #region 处理sql语句或其他类型
        public IEnumerable<T> Query(string sql, object parameters)
        {
            try
            {
                InitialState();
                return connection.Query<T>(sql, parameters, transaction, commandType: CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public T QueryModel(string sql, object parameters)
        {
            try
            {
                return Query(sql, parameters).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public IEnumerable<T2> Query<T2>(string sql, object parameters)
        {
            try
            {
                InitialState();
                return connection.Query<T2>(sql, parameters, transaction, commandType: CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public T2 QueryModel<T2>(string sql, object parameters)
        {
            try
            {
                return Query<T2>(sql, parameters).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public bool Execute(string sql, object parameters)
        {
            try
            {
                InitialState();
                connection.Execute(sql, parameters, transaction, commandType: CommandType.Text);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        public object ExecuteScalar(string sql, object parameters)
        {
            try
            {
                InitialState();
                return connection.ExecuteScalar(sql, parameters, transaction, commandType: CommandType.Text);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (transaction == null && connection != null && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        #endregion

        private void AfterSelect(T obj)
        {
            ClearChangedProperty(obj);
            SyncReference(obj);
            SyncDetail(obj);
        }

        private void AfterSelect(IEnumerable<T> objList)
        {
            ClearChangedProperty(objList);
            SyncReference(objList);
            SyncDetail(objList);
        }

        /// <summary>
        /// 查询出结果后把ChangedProperty清空
        /// </summary>
        /// <param name="obj"></param>
        private void ClearChangedProperty(T obj)
        {
            if (obj == null) return;
            obj.RecordChange = false;
            obj.RecordChange = true;
        }

        /// <summary>
        /// 查询出结果后把ChangedProperty清空
        /// </summary>
        /// <param name="objList"></param>
        private void ClearChangedProperty(IEnumerable<T> objList)
        {
            if (!objList.HasData()) return;
            for (int i = 0; i < objList.Count(); i++)
            {
                ClearChangedProperty(objList.ElementAt(i));
            }
        }

        /// <summary>
        /// 查询出结果后同步引用属性
        /// </summary>
        /// <param name="obj"></param>
        private void SyncReference(T obj) 
        {
            if (!syncReference || obj == null) return;
            SyncReferenceHelper.SyncReferenceProperty(obj);
        }

        /// <summary>
        /// 查询出结果后同步引用属性
        /// </summary>
        /// <param name="objList"></param>
        private void SyncReference(IEnumerable<T> objList)
        {
            if (!syncReference || !objList.HasData()) return;
            foreach (var obj in objList)
            {
                SyncReference(obj);
            }
        }

        /// <summary>
        /// 查询出结果后同步子表详情
        /// </summary>
        /// <param name="obj"></param>
        private void SyncDetail(T obj)
        {
            if (!syncDetail || obj == null) return;
            SyncDetailHelper.SyncDetail(obj);
        }

        /// <summary>
        /// 查询出结果后同步子表详情
        /// </summary>
        /// <param name="objList"></param>
        private void SyncDetail(IEnumerable<T> objList)
        {
            if (!syncDetail || !objList.HasData()) return;
            foreach (var obj in objList)
            {
                SyncDetail(obj);
            }
        }

        internal void InitialState()
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            if (transactionContext == null || (transactionContext != null && transactionContext.Transaction == null))
            {
                transaction = null;
            }
            else if ((transaction == null || transaction.Connection == null) && transactionContext != null && transactionContext.Transaction != null && transactionContext.Transaction.Connection != null)
            {
                transaction = transactionContext.Transaction;
            }
        }
    }
}
